package com.example.test_activity.dao;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by 旭 on 2017/3/24/024.
 */

public class DBSyllabus extends SQLiteOpenHelper {
    public static DBSyllabus dbSyllabus;
    private String mStudent_Id = "u";

    public static DBSyllabus instance(Context context, String student_id) {
//        if (dbSyllabus == null) {
            dbSyllabus = new DBSyllabus(context, student_id);
//        }

        return dbSyllabus;
    }

    public DBSyllabus(Context context, String student_id) {
        super(context, "syllabus.db", null, 1);
        this.mStudent_Id = mStudent_Id + student_id;
    }

    //当数据库不存在是调用
    @Override
    public void onCreate(SQLiteDatabase db) {
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public void onCreateTable()
    {
        SQLiteDatabase db = getWritableDatabase();
        String syllabusCreateSql = "create table " + mStudent_Id + " (Id integer primary key autoincrement," +
                "name varchar," +
                "weekend varchar," +
                "num varchar," +
                "max varchar," +
                "min varchar," +
                "flag varchar," +
                "one varchar," +
                "two varchar," +
                "week varchar," +
                "cno varchar," +
                "number varchar," +
                "school varchar)";

        db.execSQL(syllabusCreateSql);
        db.close();
    }

    public void saveSyllabus(List<Map<String, String>> list) {
        if (!tableIsExist())
        {
            onCreateTable();
        }

        SQLiteDatabase writeDb = getWritableDatabase();
        ContentValues contentValues = new ContentValues();

        for (Map<String, String> map : list) {
            for (String key : map.keySet()) {
                contentValues.put(key, map.get(key));
            }
            writeDb.insert(mStudent_Id, null, contentValues);
        }
        writeDb.close();
    }

    public List<Map<String, String>> getAllSyllabus() {
        deleteSpilthData();

        List<Map<String, String>> list = new ArrayList<>();
        SQLiteDatabase readDb = getWritableDatabase();
        String sql = "select name,weekend,num,max,min,flag,one,two,week,cno,school from " + mStudent_Id;
        Cursor cursor = readDb.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            Map<String, String> map = new HashMap<>();

            map.put("name", cursor.getString(cursor.getColumnIndex("name")));
            map.put("weekend", cursor.getString(cursor.getColumnIndex("weekend")));
            map.put("num", cursor.getString(cursor.getColumnIndex("num")));
            map.put("max", cursor.getString(cursor.getColumnIndex("max")));
            map.put("min", cursor.getString(cursor.getColumnIndex("min")));
            map.put("flag", cursor.getString(cursor.getColumnIndex("flag")));
            map.put("one", cursor.getString(cursor.getColumnIndex("one")));
            map.put("two", cursor.getString(cursor.getColumnIndex("two")));
            map.put("week", cursor.getString(cursor.getColumnIndex("week")));
            map.put("cno", cursor.getString(cursor.getColumnIndex("cno")));
            map.put("school", cursor.getString(cursor.getColumnIndex("school")));

            list.add(map);
        }
        cursor.close();
        readDb.close();

        return list;

    }

    //删除重复数据
    private void deleteSpilthData() {
        SQLiteDatabase db = getWritableDatabase();
        String sql = "delete from " + mStudent_Id + " where number in (select number from " + mStudent_Id + " group by number having count(number)>1)" +
                "and Id not in (select min(Id) from " + mStudent_Id + " group by number having count(number)>1)";
        db.execSQL(sql);
        db.close();
    }

    public void deleteData(Map<String,String> map)
    {
        SQLiteDatabase db = getWritableDatabase();
        String sql = "delete from "+mStudent_Id+" where name = '"+map.get("name")+"' and num = '"+map.get("num")+"' and weekend = '"+map.get("weekend")+"'";
        db.execSQL(sql);
        db.close();
    }

    //判断表是否存在
    public boolean tableIsExist() {
        boolean result = false;
        if (mStudent_Id == null) {
            return false;
        }
        SQLiteDatabase database = getReadableDatabase();
        String sql = "select count(*) from sqlite_master where type='table'and name='"  + mStudent_Id + "' ";
        Cursor cursor = database.rawQuery(sql, null);
        if (cursor.moveToNext())
        {
            int count = cursor.getInt(0);
            if (count > 0)
            {
                result = true;
            }
        }
        cursor.close();
        database.close();
        return result;
    }
}
